const Mydb = require('../utils/mydb');

let util = {
    /**
     * 添加
     * @param {String} tablename 表名
     * @param {Object} data 添加的键值对
     * @returns {Object} Promise对象
     */
    add: function (tablename, data = {}) {

        let fieldname = '', //字段名
            fieldval = ''; //字段值

        for (let x in data) {
            fieldname += (fieldname == '') ? x : ',' + x;
            fieldval += (fieldval == '') ? "'" + data[x] + "'" : ',' + "'" + data[x] + "'";
        }
        let sql = `INSERT INTO ${tablename} (${fieldname}) VALUES (${fieldval})`;
        return Mydb.query(sql, params = [])
    },
    //示例:
    // util.add('userform', {username: 'admin'}).then(
    //     result => {
    //         console.log('ok');
    //     }
    // )


    /**
     * 删除
     * @param {String} tablename 
     * @param {String} condition 
     * @returns {Object} Promise对象
     */
    del: function(tablename, condition = '1=0') {
        let sql = `DELETE FROM ${tablename} WHERE ${condition}`;
        return Mydb.query(sql);
    },
     //示例:
    // util.delete('userform', 'uid=12').then(
    //     result => {
    //         console.log('ok');
    //     }
    // )


    /**
     * 修改
     * @param {String} tablename 表名
     * @param {String} condition 条件
     * @param {Object} data 修改的键值对 
     * @param {Array} param 修改的字段值
     * @returns {Object} Promise对象
     */
    update: function(tablename, condition, data, param = []) {
        let sql = `UPDATE ${tablename} SET `;
        let modifyName = '',
            params = [];
        
        for (let x in data) {
            modifyName += (modifyName == '') ? `${x}=?` : `,${x}=?`;
            param.push(data[x]);
        }
        if (param.length > 0) {
            params = params.concat(param);
        }
        sql += `${modifyName} WHERE ${condition}`;
        console.log(sql);
        return Mydb.query(sql, params);
    },
    //示例:
    //  util.update('userform', 'uid=10', {username:'root',userpasswd:'222'}).then(
    //     result => {
    //         console.log('ok');
    //     }
    //  )


    /**
     * 单表查询
     * @param {String} tablename 表名
     * @param {String} fieldname 查找的字段名
     * @param {String} condition 条件
     * @param {Array} params 条件参数
     * @returns {Object} Promise对象
     */
    singleSelect: function(tablename, fieldname, condition, params = []) {
        let fieldnames = '';
        if (fieldname instanceof Array) {
            for (let x in fieldname) {
                fieldnames += (fieldnames == '') ? fieldname[x] : `,${fieldname[x]}`
            }
        } else {
            fieldnames = fieldname;
        }

        let sql = `SELECT ${fieldnames} FROM ${tablename} WHERE ${condition}`;
        console.log("Mydb -sql",sql);
        return Mydb.query(sql, params, true);
    },
    //示例:
    // util.singleSelect('userform', 'uid', "username='root'").then(
    //     result => {
    //         console.log(result);
    //     }
    // )

    /**
     * 多条查询
     * @param {String} tablename 表名
     * @param {String} fieldname 查找的字段名
     * @param {String} condition 条件
     * @param {Array} params 条件参数
     * @param {String} order 升序降序  order by DESC/ASC
     * @param {String} limit 限制条数  LIMIT 0,2
     * @returns {Object} Promise对象
     */
    multiSelect: function (tablename, fieldname, condition, params = [], order = '', limit = '') {
        let fieldnames = '';
        if (fieldname instanceof Array) {
            for (let x in fieldname) {
                fieldnames += (fieldnames == '') ? fieldname[x] : `,${fieldname[x]}`
            }
        } else {
            fieldnames = fieldname;
        }
        
        let sql = `SELECT ${fieldnames} FROM ${tablename} WHERE ${condition} ${order} ${limit}`;


        if(condition==null){
            sql = `SELECT ${fieldnames} FROM ${tablename} ${order} ${limit}`;
        }
        console.log(sql);

        return Mydb.query(sql, params)
    },
    //示例:
    // util.multiSelect('userform', 'uid', "username='root'",[], 'ORDER BY uid DESC').then(
    //     result => {
    //         console.log(result);
    //     }
    // )


    /**
     * 
     * @param {*} feileds  查询的字段名
     * @param {*} tablename1  查询表1
     * @param {*} tablename2  查询表2
     * @param {*} fieldnames   查询的字段名
     * @param {*} condition   条件参数
     * @returns 
     */
    relatedSelect:  function(feiled,tablename1,tablename2,fieldnames,condition,order = '') {
        // let feileds = '';
        // if (feiled instanceof Array) {
        //     for (let x in feiled) {
        //         feileds += (feileds == '') ? feiled[x] : `,${feiled[x]}`
        //     }
        // } else {
        //     feileds = feiled;
        // }
        let sql = `SELECT ${feiled} from ${tablename1} as a INNER JOIN ${tablename2} as b ON a.${fieldnames} = b.${fieldnames}  WHERE ${condition} ${order}` ;
        console.log("relatedSelect -sql",sql);
        return Mydb.query(sql);
    },

    //示例
    // async function(req,res) {
    //     return await db.relatedSelect('*', 'study_section', 'study_course', 'course_id', `a.course_id = ${req.body.courseid}`);
    // }
    sqlSelect:  function(sql) {
        // let feileds = '';
        // if (feiled instanceof Array) {
        //     for (let x in feiled) {
        //         feileds += (feileds == '') ? feiled[x] : `,${feiled[x]}`
        //     }
        // } else {
        //     feileds = feiled;
        // }
        //let sql = `SELECT ${feiled} from ${tablename1} as a INNER JOIN ${tablename2} as b ON a.${fieldnames} = b.${fieldnames}  WHERE ${condition} ${order}` ;
        console.log("relatedSelect -sql",sql);
        return Mydb.query(sql);
    }
}
module.exports = util;